import mydb
import config_json
import openpyxl
import traceback
import getpath
import myglobal
import os
from PyQt5 import QtWidgets
import matplotlib.pyplot as plt
import numpy as np
import matplotlib
from PIL import Image as PILImage
from openpyxl.drawing.image import Image
import io
matplotlib.use('AGG')
class Spec:
    def __init__(self):
        pass
    def to_fields(self,data):
        fields = [{"ele": None, "name": "id", "title": "id", "visible": False
                        }, {"ele": None, "name": "sampleid", "title": "样品编号", "visible": True
                            }, {"ele": None, "name": "beizhu", "title": "备注", "visible": True
                                }]       
        if len(data)>3:
            for one in data[3:]:
                fields.append({"ele": one[:-1], "name": one, "title": one[:-1], "visible": True})
        return fields 
    def spec_output_curve(self,fields,ws,hang):
        ts=[]
        eles=[]
        for f in fields:
            if f["visible"] and f.get("ele") != None:
                eles.append(f.get("ele"))
        ts=self.getImages(eles)
        self.outputImages(ws,hang,ts)
    def getImages(self,eles):
        r=[]
        for ele in eles:
            curves=self.getImage_ele(ele)
            for curve in curves:
                r.append(curve)
        return r
    def getImage_ele(self,ele):
        xishus=myglobal.db.getXishuAll(myglobal.methodid,ele)
        rt=[]
        if len(xishus)==0:
            data=myglobal.db.getCurveData(myglobal.methodid,ele,0)
            params=[]
            params.append("ele="+ele)
            params.append("")
            params.append("")
            params.append("")
            params.append("")
            rt.append((params,data,ele))
        else:
            for xishu in xishus:
                [x2,x1,x0,r,curveid,must00,ci]=xishu
                data=myglobal.db.getCurveData(myglobal.methodid,ele,curveid)
                params=[]
                params.append("ele="+ele)
                params.append("x2="+str(x2))
                params.append("x1="+str(x1))
                params.append("x0="+str(x0))
                params.append("r="+str(r))
                # filename=os.path.join(getpath.getpath(),"images",str(myglobal.methodid)+"_"+ele+"_"+str(curveid)+".jpg")
                rt.append((params,data,ele))
        return rt
    def imageFromdata(self,data):
        std=[]
        measure=[]
        yfited=[]
        for i in range(len(data)):
            std.append(float(data[i][1]))
            measure.append(float(data[i][2]))
            yfited.append(float(data[i][3]))
        # print(measure,std,yfited)
        fig, ax = plt.subplots()  # Create a figure containing a single axes.
        ax.plot(measure, std,'o')  # Plot some data on the axes.
        ax.plot(measure,yfited)
        # fig, ax = plt.subplots()  # Create a figure containing a single axes.
        # ax.plot([1, 2, 3, 4], [1, 4, 2, 3])  # Plot some data on the axes.
        # print(dir(plt))
        fio=io.BytesIO()
        plt.savefig(fio)
        plt.close()
        img=PILImage.open(fio)
        return Image(img)
        # print(dir(img))
        # img.save("a.png")

    def outputImages(self,ws,hang,ts):#outputsheet,pdf filename,0-n page
        ss=QtWidgets.qApp.screens()
        if len(ss)<1:
            rowh=13.5/72.0*96
        else:
            rowh=13.5/72.0*ss[0].physicalDotsPerInch() #bang to pixel
        at_row=hang
        for i in range(len(ts)):
            (paras,data,ele)=ts[i]
            ws["A"+str(at_row)]="sampleid"
            ws["B"+str(at_row)]=ele+"-STD"
            ws["C"+str(at_row)]=ele
            ws["D"+str(at_row)]=ele+"'"
            j=1
            if paras[1]!="":
                image=self.imageFromdata(data)
                for one in data:
                    ws["A"+str(at_row+j)]=one[0]
                    ws["B"+str(at_row+j)]=one[1]
                    ws["C"+str(at_row+j)]=one[2]
                    ws["D"+str(at_row+j)]=one[3]
                    j+=1
                # n=15
                for j in range(len(paras)):
                    ws["E"+str(at_row+j)]=paras[j]
                img =self.imageFromdata(data)# Image(os.path.join(folder,img))
                img.width=300#not too big
                img.height=200
                # print(dir(img))
                n=int((img.height)/rowh)
                ws.add_image(img, "F"+str(at_row))  # 图片 插入 A1 的位置上
            else:
                for one in data:
                    ws["A"+str(at_row+j)]=one[0]
                    ws["B"+str(at_row+j)]=one[1]
                    ws["C"+str(at_row+j)]=one[2]
                    ws["D"+str(at_row+j)]=one[3]
                    j+=1
                # n=15
                for j in range(len(paras)):
                    ws["E"+str(at_row+j)]=paras[j]
                n=1
            # self.chart(ws,2,3,4,at_row,at_row+len(data),ele)
            if len(data)+1>n:
                at_row=at_row+len(data)+1
            else:
                at_row=at_row+n
    def chart(self,ws,ycol,xcol,ycol2,min_row,max_row,title):
        chart = openpyxl.chart.ScatterChart()
        chart.title = title
        chart.style = 13
        chart.legend = None
        # chart.x_axis.title = 'Size'
        # chart.y_axis.title = 'Percentage'

        xvalues = openpyxl.chart.Reference(ws, min_col=xcol, min_row=min_row+1, max_row=max_row)

        yvalues = openpyxl.chart.Reference(ws, min_col=ycol, min_row=min_row+1, max_row=max_row)
        series1 = openpyxl.chart.Series(yvalues, xvalues, title_from_data=False)
        series1.marker.symbol = "circle"
        series1.marker.graphicalProperties.solidFill = "FF0000"  # 点的内部填充颜色
        series1.marker.graphicalProperties.line.solidFill = "FF0000"  #点的外边框颜色
        series1.graphicalProperties.line.noFill = True
        chart.series.append(series1)
        # print(dir(series1))
        yvalues2 = openpyxl.chart.Reference(ws, min_col=ycol2, min_row=min_row+1, max_row=max_row)
        series2 = openpyxl.chart.Series(yvalues2, xvalues, title_from_data=False)
        chart.series.append(series2)
        cell=openpyxl.utils.cell.get_column_letter(ycol2+2)+str(min_row)
        ws.add_chart(chart, cell)#ycol2+2,min_row
    def findSTD(self):
        pass
def test_outputimages():        
    myglobal.db=mydb.MyDb()
    myglobal.methodid=myglobal.db.methodid()
    spec=Spec()
    myglobal.fields=spec.to_fields(myglobal.db.table_info("STD_result"))
    wb = openpyxl.Workbook()
    ws = wb.active
    hang=1
    spec.spec_output_curve(myglobal.fields,ws,hang)
    wb.save("imags.xlsx")
def test_findSTD():    
    myglobal.db=mydb.MyDb()
    spec=Spec()
    columns=myglobal.db.table_info("STD_result")
    cmd = """select * from STD_result"""
    self.q.exec_(cmd)
    while self.q.next():
        r=[]
        for i in range(len(columns)):
            r.append(self.q.value(i))
        break
    print(r)
if __name__=="__main__":
    # test_findSTD()
    test_outputimages()